/**
 * SearchInfoDAO class.
 */
package pits.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import pits.dto.EmployeeDTO;
import pits.dto.PreSearchDTO;
import pits.form.SearchInfoForm;
import pits.util.Constants;
import pits.util.Logger;

/**
 * DAO class for SearchInfoPortlet.
 * 
 * @author Sthy
 * 
 */
public class SearchInfoDAO {

	public PreSearchDTO getData() {
		PreSearchDTO result = new PreSearchDTO();
		List<String> companyNames = new ArrayList<String>();
		List<Integer> companyIDs = new ArrayList<Integer>();

		List<List<String>> departNames = new ArrayList<List<String>>();
		List<List<Integer>> departIDs = new ArrayList<List<Integer>>();

		List<String> eachComDepNames = null;
		List<Integer> eachComDepIDs = null;

		List<List<List<String>>> courseNames = new ArrayList<List<List<String>>>();
		List<List<List<Integer>>> courseIDs = new ArrayList<List<List<Integer>>>();

		List<List<String>> eachComCourseNames = null;
		List<List<Integer>> eachComCourseIDs = null;

		List<String> eachDepCourseNames = null;
		List<Integer> eachDepCourseIDs = null;

		List<List<List<String>>> projectNames = new ArrayList<List<List<String>>>();
		List<List<List<Integer>>> projectIDs = new ArrayList<List<List<Integer>>>();

		List<List<String>> eachComProjectNames = null;
		List<List<Integer>> eachComProjectIDs = null;

		List<String> eachDepProjectNames = null;
		List<Integer> eachDepProjectIDs = null;

		List<String> langNames = new ArrayList<String>();
		List<Integer> langIDs = new ArrayList<Integer>();

		List<String> appNames = new ArrayList<String>();
		List<Integer> appIDs = new ArrayList<Integer>();

		List<String> techNames = new ArrayList<String>();
		List<Integer> techIDs = new ArrayList<Integer>();

		List<String> frameNames = new ArrayList<String>();
		List<Integer> frameIDs = new ArrayList<Integer>();

		Connection conn = null;
		String query = Constants.BLANK_VALUE;
		PreparedStatement sqlStatement = null;
		ResultSet comResultSet = null;
		ResultSet departResultSet = null;
		ResultSet courseResultSet = null;
		ResultSet projectResultSet = null;
		ResultSet langResultSet = null;
		ResultSet appResultSet = null;
		ResultSet techResultSet = null;
		ResultSet frameResultSet = null;

		try {
			query = "SELECT * FROM company WHERE isDeleted=0";

			conn = pits.util.Connection.getConnection();
			sqlStatement = conn.prepareStatement(query);

			// new Logger().write(query);

			comResultSet = sqlStatement.executeQuery();

			while (comResultSet.next()) {
				query = "SELECT * FROM department" + " WHERE CompanyID=" + comResultSet.getInt("CompanyID")
				        + " AND isDeleted=0";

				// new Logger().write(query);

				sqlStatement = conn.prepareStatement(query);
				departResultSet = sqlStatement.executeQuery();

				eachComDepNames = new ArrayList<String>();
				eachComDepIDs = new ArrayList<Integer>();
				eachComCourseNames = new ArrayList<List<String>>();
				eachComCourseIDs = new ArrayList<List<Integer>>();
				eachComProjectNames = new ArrayList<List<String>>();
				eachComProjectIDs = new ArrayList<List<Integer>>();

				while (departResultSet.next()) {
					// new
					// Logger().write(departResultSet.getString("DepartName"));
					eachComDepNames.add(departResultSet.getString("DepartName"));
					eachComDepIDs.add(departResultSet.getInt("DepartID"));

					query = "SELECT * FROM course " + "WHERE DepartID=" + departResultSet.getInt("DepartID") + " "
					        + "AND isDeleted=0";
					// new pits.util.Logger().write(query);

					sqlStatement = conn.prepareStatement(query);
					courseResultSet = sqlStatement.executeQuery();

					eachDepCourseNames = new ArrayList<String>();
					eachDepCourseIDs = new ArrayList<Integer>();

					while (courseResultSet.next()) {
						// new
						// Logger().write(courseResultSet.getString("Name"));
						eachDepCourseNames.add(courseResultSet.getString("Name"));
						eachDepCourseIDs.add(courseResultSet.getInt("CourseID"));
					}

					eachComCourseNames.add(eachDepCourseNames);
					eachComCourseIDs.add(eachDepCourseIDs);

					query = "SELECT * FROM project " + "WHERE DepartID=" + departResultSet.getInt("DepartID") + " "
					        + "AND isDeleted=0";
					// new pits.util.Logger().write(query);

					sqlStatement = conn.prepareStatement(query);
					projectResultSet = sqlStatement.executeQuery();

					eachDepProjectNames = new ArrayList<String>();
					eachDepProjectIDs = new ArrayList<Integer>();

					while (projectResultSet.next()) {
						// new
						// Logger().write(projectResultSet.getString("ProjectName"));
						eachDepProjectNames.add(projectResultSet.getString("ProjectName"));
						eachDepProjectIDs.add(projectResultSet.getInt("ProjectID"));
					}

					eachComProjectNames.add(eachDepProjectNames);
					eachComProjectIDs.add(eachDepProjectIDs);
				}
				// new Logger().write(comResultSet.getString("ComName"));
				companyNames.add(comResultSet.getString("ComName"));
				companyIDs.add(comResultSet.getInt("CompanyID"));
				departNames.add(eachComDepNames);
				departIDs.add(eachComDepIDs);
				courseNames.add(eachComCourseNames);
				courseIDs.add(eachComCourseIDs);
				projectNames.add(eachComProjectNames);
				projectIDs.add(eachComProjectIDs);
				// new pits.util.Logger().write("" + projectNames.size());
			}

			// new Logger().writeln("getComList: done");

			query = "SELECT * FROM language WHERE isDeleted=0";
			sqlStatement = conn.prepareStatement(query);
			langResultSet = sqlStatement.executeQuery();
			while (langResultSet.next()) {
				langNames.add(langResultSet.getString("Name"));
				langIDs.add(langResultSet.getInt("LangID"));
			}

			query = "SELECT * FROM application WHERE isDeleted=0";
			sqlStatement = conn.prepareStatement(query);
			appResultSet = sqlStatement.executeQuery();
			while (appResultSet.next()) {
				appNames.add(appResultSet.getString("Name"));
				appIDs.add(appResultSet.getInt("AppID"));
			}

			query = "SELECT * FROM technology WHERE isDeleted=0";
			sqlStatement = conn.prepareStatement(query);
			techResultSet = sqlStatement.executeQuery();
			while (techResultSet.next()) {
				techNames.add(techResultSet.getString("Name"));
				techIDs.add(techResultSet.getInt("TechID"));
			}

			query = "SELECT * FROM framework WHERE isDeleted=0";
			sqlStatement = conn.prepareStatement(query);
			frameResultSet = sqlStatement.executeQuery();
			while (frameResultSet.next()) {
				frameNames.add(frameResultSet.getString("Name"));
				frameIDs.add(frameResultSet.getInt("FrameID"));
			}

			// new Logger().writeln("getCommonData: done");

		} catch (SQLException sqlEx) {
			new Logger().writeln(sqlEx.toString());
		} finally {
			try {
				frameResultSet.close();
				techResultSet.close();
				langResultSet.close();
				departResultSet.close();
				comResultSet.close();
				sqlStatement.close();
				conn.close();
			} catch (SQLException sqlEx) {
				new Logger().writeln(sqlEx.toString());
			}
		}
		result.setCompanyNames(companyNames);
		result.setCompanyIDs(companyIDs);
		result.setDepartNames(departNames);
		result.setDepartIDs(departIDs);
		result.setCourseNames(courseNames);
		result.setCourseIDs(courseIDs);
		result.setProjectNames(projectNames);
		result.setProjectIDs(projectIDs);
		result.setAppIDs(appIDs);
		result.setAppNames(appNames);
		result.setFrameIDs(frameIDs);
		result.setFrameNames(frameNames);
		result.setLangIDs(langIDs);
		result.setLangNames(langNames);
		result.setTechIDs(techIDs);
		result.setTechNames(techNames);

		// new Logger().writeln("getData: done");

		return result;
	}

	public List<EmployeeDTO> doSearch(SearchInfoForm formSearchInfo) {
		EmployeeDTO dto;
		List<EmployeeDTO> result = new ArrayList<EmployeeDTO>();
		Connection conn = null;
		String query = null;
		ResultSet rs = null;
		PreparedStatement sqlStatement = null;
		boolean firstCondition = true;

		try {
			conn = pits.util.Connection.getConnection();

			query = "SELECT DISTINCT e.* FROM employee e, department d , course c, courseexp ce, project p, projectexp pe ";

			if (formSearchInfo.getEmployeeName() != null && !formSearchInfo.getEmployeeName().equals("")) {
				query += "WHERE INSTR(e.Name, '" + formSearchInfo.getEmployeeName() + "') > 0 ";
				firstCondition = false;
			}

			if (!formSearchInfo.getCompanyID().equals("All")) {
				if (!firstCondition) {
					query += "AND ";
				} else {
					query += "WHERE ";
				}

				if (formSearchInfo.getDepartmentID().equals("All")) {
					query += "e.DepartID IN (" + "SELECT com_d.DepartID FROM department com_d "
					        + "WHERE com_d.CompanyID = " + formSearchInfo.getCompanyID() + ") ";
				} else {
					query += "e.DepartID = " + formSearchInfo.getDepartmentID() + " ";
				}

				firstCondition = false;
			}

			// new pits.util.Logger().writeln(query);

			if (!formSearchInfo.getCourseCom().equals("All")) {
				if (!firstCondition) {
					query += "AND ";
				} else {
					query += "WHERE ";
				}
				query += "e.EmpID = ce.EmpID " + "AND c.CourseID = ce.CourseID ";

				if (!formSearchInfo.getCourseDep().equals("All")) {
					if (!formSearchInfo.getCourseID().equals("All")) {
						query += "AND c.CourseID = " + formSearchInfo.getCourseID() + " ";
					} else {
						// find all the courses in DepartmentID
						query += "AND c.CourseID IN (" + "SELECT CourseID FROM course " + "WHERE DepartID = "
						        + formSearchInfo.getCourseDep() + ") ";
					}
				} else {
					// find all the courses in CompanyID
					query += "AND c.CourseID IN ("
					        + "SELECT course_c.CourseID FROM course course_c, department course_d "
					        + "WHERE course_c.DepartID = course_d.DepartID " + "AND course_d.DepartID IN ("
					        + "SELECT course_dd.DepartID FROM department course_dd " + "WHERE course_dd.CompanyID = "
					        + formSearchInfo.getCourseCom() + ")" + ") ";
				}

				if (!formSearchInfo.getGrade().equals("All")) {
					query += "AND ce.grade = '" + formSearchInfo.getGrade() + "' ";
				}
				firstCondition = false;
			}

			// new pits.util.Logger().writeln(query);

			if (!formSearchInfo.getProjectCom().equals("All")) {
				if (!firstCondition) {
					query += "AND ";
				} else {
					query += "WHERE ";
				}
				query += "e.EmpID = pe.EmpID " + "AND p.ProjectID = pe.ProjectID ";

				if (!formSearchInfo.getProjectDep().equals("All")) {
					if (!formSearchInfo.getProjectID().equals("All")) {
						query += "AND p.ProjectID = " + formSearchInfo.getProjectID() + " ";
					} else {
						// find all the projects in DepartmentID
						query += "AND p.ProjectID IN (" + "SELECT ProjectID FROM project " + "WHERE DepartID = "
						        + formSearchInfo.getProjectDep() + ") ";
					}
				} else {
					// find all the projects in CompanyID
					query += "AND p.ProjectID IN ("
					        + "SELECT project_p.ProjectID FROM project project_p, department project_d "
					        + "WHERE project_p.DepartID = project_d.DepartID " + "AND project_d.DepartID IN ("
					        + "SELECT project_dd.DepartID FROM department project_dd "
					        + "WHERE project_dd.CompanyID = " + formSearchInfo.getProjectCom() + ")" + ") ";
				}

				firstCondition = false;
			}

			// new pits.util.Logger().writeln(query);

			// new pits.util.Logger().writeln("Start Technology Conditions");

			if (formSearchInfo.getLanguage().size() > 0) {
				for (int i = 0; i < formSearchInfo.getLanguage().size(); i++) {
					if (!firstCondition) {
						query += "AND ";
					} else {
						query += "WHERE ";
					}
					query += "e.EmpID IN ("
					        + "SELECT lang_e.EmpID FROM employee lang_e, projectexp lang_pe, project lang_p, project_language lang_pl "
					        + "WHERE lang_e.EmpID = lang_pe.EmpID "
					        + "AND lang_p.ProjectID = lang_pe.ProjectID "
					        + "AND lang_p.ProjectID = lang_pl.ProjectID "
					        + "AND lang_pl.LangID = "
					        + formSearchInfo.getLanguage().get(i)
					        + " "
					        + "AND (DATEDIFF(STR_TO_DATE(lang_p.periodTo,'%d/%m/%Y'), STR_TO_DATE(lang_p.periodFrom,'%d/%m/%Y')))/30 >= "
					        + formSearchInfo.getLangMOE().get(i) + ") ";
					firstCondition = false;
				}
			}

			// new pits.util.Logger().writeln(query);

			if (formSearchInfo.getApplication().size() > 0) {
				for (int i = 0; i < formSearchInfo.getApplication().size(); i++) {
					if (!firstCondition) {
						query += "AND ";
					} else {
						query += "WHERE ";
					}
					query += "e.EmpID IN ("
					        + "SELECT app_e.EmpID FROM employee app_e, projectexp app_pe, project app_p, project_application app_pa "
					        + "WHERE app_e.EmpID = app_pe.EmpID "
					        + "AND app_p.ProjectID = app_pe.ProjectID "
					        + "AND app_p.ProjectID = app_pa.ProjectID "
					        + "AND app_pa.AppID = "
					        + formSearchInfo.getApplication().get(i)
					        + " "
					        + "AND (DATEDIFF(STR_TO_DATE(app_p.periodTo,'%d/%m/%Y'), STR_TO_DATE(app_p.periodFrom,'%d/%m/%Y')))/30 >= "
					        + formSearchInfo.getAppMOE().get(i) + ") ";
					firstCondition = false;
				}
			}

			// new pits.util.Logger().writeln(query);

			if (formSearchInfo.getTechnology().size() > 0) {
				for (int i = 0; i < formSearchInfo.getTechnology().size(); i++) {
					if (!firstCondition) {
						query += "AND ";
					} else {
						query += "WHERE ";
					}
					query += "e.EmpID IN ("
					        + "SELECT tech_e.EmpID FROM employee tech_e, projectexp tech_pe, project tech_p, project_technology tech_pt "
					        + "WHERE tech_e.EmpID = tech_pe.EmpID "
					        + "AND tech_p.ProjectID = tech_pe.ProjectID "
					        + "AND tech_p.ProjectID = tech_pt.ProjectID "
					        + "AND tech_pt.TechID = "
					        + formSearchInfo.getTechnology().get(i)
					        + " "
					        + "AND (DATEDIFF(STR_TO_DATE(tech_p.periodTo,'%d/%m/%Y'), STR_TO_DATE(tech_p.periodFrom,'%d/%m/%Y')))/30 >= "
					        + formSearchInfo.getTechMOE().get(i) + ") ";
					firstCondition = false;
				}
			}

			// new pits.util.Logger().writeln(query);

			if (formSearchInfo.getFramework().size() > 0) {
				for (int i = 0; i < formSearchInfo.getFramework().size(); i++) {
					if (!firstCondition) {
						query += "AND ";
					} else {
						query += "WHERE ";
					}
					query += "e.EmpID IN ("
					        + "SELECT frame_e.EmpID FROM employee frame_e, projectexp frame_pe, project frame_p, project_framework frame_pf "
					        + "WHERE frame_e.EmpID = frame_pe.EmpID "
					        + "AND frame_p.ProjectID = frame_pe.ProjectID "
					        + "AND frame_p.ProjectID = frame_pf.ProjectID "
					        + "AND frame_pf.FrameID = "
					        + formSearchInfo.getFramework().get(i)
					        + " "
					        + "AND (DATEDIFF(STR_TO_DATE(frame_p.periodTo,'%d/%m/%Y'), STR_TO_DATE(frame_p.periodFrom,'%d/%m/%Y')))/30 >= "
					        + formSearchInfo.getFrameMOE().get(i) + ") ";
					firstCondition = false;
				}
			}

			// new pits.util.Logger().writeln(query);

			sqlStatement = conn.prepareStatement(query);
			rs = sqlStatement.executeQuery();
			while (rs.next()) {
				dto = new EmployeeDTO();
				dto.setEmpID(rs.getInt("EmpID"));
				dto.setName(rs.getString("Name"));
				dto.setBirthday(rs.getString("Birthday"));
				dto.setAddress(rs.getString("Address"));
				dto.setEmail(rs.getString("Email"));
				dto.setPhone(rs.getString("Phone"));
				result.add(dto);
			}
		} catch (Exception sqlEx) {
			new pits.util.Logger().writeln(sqlEx.toString());
		} finally {
			try {
				rs.close();
				sqlStatement.close();
				conn.close();
			} catch (SQLException sqlEx) {
				new pits.util.Logger().writeln(sqlEx.toString());
			}
		}
		return result;
	}
}
